﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Article : Article_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Article(int id = -1,
                        int categoryId = 0,
                        string languageId = "",
                        string image = "",
                        string title = "", 
                        string brief = "",                        
                        string contents = "",
                        string tag = "",
                        string type="",
                        int orders = 0,
                        bool published = true)
        {
            this.Id = id;
            this.CategoryId = categoryId;
            this.LanguageId = languageId;
            this.Image = image;
            this.Title = title;
            this.Brief = brief;            
            this.Contents = contents;
            this.Tag = tag;
            this.Type = type;
            this.Orders = orders;
            this.Published = published;
            this.DisplayDate = DateTime.Now;
        }
        #endregion

        #region Lists
        public DataTable Filter(string fromDate, string toDate, string SortColumn, string categoryList = "", bool isTransaction = false)
        {
            string strQuery = "select a.Id, CategoryId, Title, Image, Orders, Tag, Views, Published, count(c.Id) as Comment from Article a left join Article_Language l on a.Id=l.ArticleId and LanguageID=@LanguageID left join Article_Comment c on a.Id=c.ArticleId where "; 
            if (SortColumn == "") SortColumn = "Orders";
            if (categoryList != "" && fromDate != toDate)
                strQuery += "(DisplayDate between @FromDate and @ToDate) and CategoryId in (" + categoryList + ") and Deleted=0 order by " + SortColumn;
            else if (categoryList != "")
                strQuery += "CategoryId in (" + categoryList + ") and Deleted=0 group by a.Id, CategoryId, Title, Image, Orders, Tag, Views, Published order by " + SortColumn;
            else
                strQuery += "Deleted=0 group by a.Id, CategoryId, Title, Image, Orders, Tag, Views, Published order by " + SortColumn;

            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            SqlParameter FromDate = new SqlParameter("@FromDate", fromDate);
            SqlParameter ToDate = new SqlParameter("@ToDate", toDate);
            return ExecuteDataset(isTransaction, strQuery, LanguageId, FromDate, ToDate).Tables[0];
        }

        public bool Delete(int delete, int id, bool isTransaction = false)
        {
            string strQuery = "update Article set Deleted=@Deleted, Published=@Published ,ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete == 1) ? user : "");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, P, D, User, Now, DeleteByUser) > 0;
        }

        public bool Publish(int publish, int id, bool isTransaction = false)
        {
            string strQuery = "update Article set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, P, Now, User) > 0;
        }

        public bool Order(string order, int id, bool isTransaction = false)
        {
            string strQuery = "update Article set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, Now, User) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle(bool isTransaction = false)
        {
            if (Exist(isTransaction))
            {
                string strQuery = "select CategoryId, Title, Image, Brief, Contents, Tag, Type, Orders, Published, DisplayDate from Article a left join Article_Language l on Id=ArticleId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(isTransaction, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Brief = dt.Rows[0]["Brief"].ToString();
                    this.Contents = dt.Rows[0]["Contents"].ToString();
                    this.Tag = dt.Rows[0]["Tag"].ToString();
                    this.Type = dt.Rows[0]["Type"].ToString();
                    this.CategoryId = Convert.ToInt32(dt.Rows[0]["CategoryId"]);
                    if(!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];                    
                    if (!string.IsNullOrEmpty(dt.Rows[0]["DisplayDate"].ToString())) 
                        this.DisplayDate = (DateTime)dt.Rows[0]["DisplayDate"];
                }
            }
            else this.Id = -1;
        }

        public bool hasType(string type, bool isTransaction = false)
        {
            string strQuery = "select Id from Article where Type like '%" + type + "%' and Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            return ExecuteScalar(isTransaction, strQuery, Id, LanguageId) != null;
        }

        public bool Exist(bool isTransaction = false)
        {
            string strQuery = "select Id from Article where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(isTransaction, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid, bool isTransaction = false)
        {
            string strQuery = "select ArticleId from Article_Language where ArticleId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(isTransaction, strQuery, Id, LanguageId) != null;
        }

        public bool Save(bool isTransaction = true)
        {
            TransactionBegin();
            try
            {
                if (Exist(isTransaction)) // sửa
                {
                    Delete(0, this.Id, isTransaction);
                    UpdateObj(isTransaction);

                    if (Exist(this.Id, this.LanguageId, isTransaction)) UpdateObjLang(isTransaction);
                    else InsertObjLang(isTransaction);
                }
                else // thêm
                {
                    this.Id = InsertObj(isTransaction);
                    InsertObjLang(isTransaction);
                }

                TransactionCommit();
                return true;
            }
            catch
            {
                TransactionRollback();
                return false;
            }
        }

        public int InsertObj(bool isTransaction = false)
        {
            string sqlCat = "insert into Article(CategoryId, Image, Tag, Type, Orders, Published, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DisplayDate) "
                                    + "values (@CategoryId, @Image, @Tag, @Type, @Orders, @Published, 0, @Now, @Now, @User, @User, @DisplayDate)";

            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.CategoryId);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            SqlParameter DisplayDate = new SqlParameter("@DisplayDate", this.DisplayDate);

            return ExecuteNonQuery(isTransaction, sqlCat, CategoryId, Now, Image, Tag, Type, Orders, Published, User, DisplayDate);
        }

        public int InsertObjLang(bool isTransaction = false)
        {
            string sqlLang = "insert into Article_Language (ArticleId, LanguageId, Title, Brief, Contents, CreateDate, ModifyDate, CreateByUser, ModifyByUser) "
                                            + "values (@ArticleId, @LanguageId, @Title, @Brief, @Contents, @Now, @Now, @User, @User)";
            SqlParameter ArticleId = new SqlParameter("@ArticleId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Brief = new SqlParameter("@Brief", this.Brief);
            SqlParameter Contents = new SqlParameter("@Contents", this.Contents);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            return ExecuteNonQuery(isTransaction, sqlLang, ArticleId, LanguageId, Title, Brief, Contents, Now, User);
        }

        public bool UpdateObj(bool isTransaction = false)
        {
            string sqlCat = "update Article set CategoryId=@CategoryId, Image=@Image, Tag=@Tag, Type=@Type, Orders=@Orders, Published=@Published, ModifyDate=@Now, ModifyByUser=@User, DisplayDate=@DisplayDate where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.CategoryId);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter DisplayDate = new SqlParameter("@DisplayDate", this.DisplayDate);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, Id, CategoryId, Image, Tag, Type, Orders, Published, DisplayDate, Now, User) > 0;
        }

        public int UpdateObjLang(bool isTransaction = false)
        {
            string sqlLang = "update Article_Language set Title=@Title, Brief=@Brief, Contents=@Contents, ModifyDate=@Now, ModifyByUser=@User where ArticleId=@ArticleId and LanguageId=@LanguageId";
            SqlParameter ArticleId = new SqlParameter("@ArticleId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Brief = new SqlParameter("@Brief", this.Brief);
            SqlParameter Contents = new SqlParameter("@Contents", this.Contents);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlLang, ArticleId, LanguageId, Title, Brief, Contents, Now, User);
        }
        #endregion

        #region Trash
        public bool CategoryIdExist(int delete, string id, bool isTransaction = false)
        {
            string strQuery = "select c.Id from Article a, Category c where a.Id=@Id and c.Deleted=@Delete and a.CategoryId=c.Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(isTransaction, strQuery, Id, d);
            return (obj != null) ? true : false;
        }

        public DataTable GetRecycleBin(string SortColumn, bool isTransaction = false)
        {
            string strQuery = "select Id, Image, CategoryId, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Article where Deleted=1 ";
            if(SortColumn != "")
                strQuery += "Order By " + SortColumn;
            return ExecuteDataset(false, strQuery).Tables[0];
        }

        public bool Cleared(string id, bool isTransaction = false)
        {
            string strQuery = "delete from Article where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(isTransaction, strQuery, Id) > 0;
        }
        #endregion
    }
}
